<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page language="java" import="com.base.myproject.client.jdbc.*" %>
<%@ page language="java" import="com.base.myproject.server.*" %>
<%@ page language="java" import="com.base.myproject.client.jdbc.DataSet" %>
<%@ page language="java" import="com.base.myproject.server.tools.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>考情异常报表</title>
<%
	String begintime = request.getParameter("beginTime");
	String endtime = request.getParameter("endTime");
	
	StringBuilder selectSql = new StringBuilder();
	StringBuilder leftSql = new StringBuilder();
	StringBuilder sql = new StringBuilder();
	String code = "";
	String name = "";
	
	DataSetTool DST = new DataSetTool();
	DataSet vacationType = DST.getDataSet("select holsCode, holsName from hr_Attendance_holsType");
	for (int i=0; i < vacationType.getRowCount(); i++) {
		code = vacationType.getValue(i, "holsCode");
		name = vacationType.getValue(i, "holsName");
		
		selectSql.append(", isnull(");
		selectSql.append(code);
		selectSql.append(",0) as ");
		selectSql.append(code);
		
		if (code.equals("EL") || code.equals("SL")) {
			leftSql.append("left join (select dates.bcode, cast(sum(vacationDates) as numeric(10,2)) as ");
			leftSql.append(code);
			leftSql.append(" from (select count(1) as vacationDates, bcode from vacationDetail where vacationday between '");
			leftSql.append(begintime);
			leftSql.append("' and '");
			leftSql.append(endtime);
			leftSql.append("' and code = '");
			leftSql.append(code);
			leftSql.append("' group by bcode union all select sum(period)*1.0/8 as times, bcode from hr_Attendance_ot where useDate between '");
			leftSql.append(begintime);
			leftSql.append("' and '");
			leftSql.append(endtime);
			leftSql.append("' and holidayType = '");
			leftSql.append(code);
			leftSql.append("' group by bcode) as dates group by bcode) as ");
			leftSql.append(code);
			leftSql.append(" on ");
			leftSql.append(code);
			leftSql.append(".bcode = bcode.bcode ");
		} else {
			leftSql.append("left join (select count(1) as ");
			leftSql.append(code);
			leftSql.append(", bcode from vacationDetail where vacationday between '");
			leftSql.append(begintime);
			leftSql.append("' and '");
			leftSql.append(endtime);
			leftSql.append("' and code = '");
			leftSql.append(code);
			leftSql.append("' group by bcode) as ");
			leftSql.append(code);
			leftSql.append(" on ");
			leftSql.append(code);
			leftSql.append(".bcode = bcode.bcode ");
		}
	}
	
	sql.append("select bcode.*");
	sql.append(selectSql.toString());
	sql.append(" from (select bcode, bname from bcode) as bcode ");
	sql.append(leftSql.toString());
	sql.append(" where exists (select 1 from vacationDetail where vacationday between '");
	sql.append(begintime);
	sql.append("' and '");
	sql.append(endtime);
	sql.append("' and bcode = bcode.bcode)");
	
	DataSet vacationCount = DST.getDataSet(sql.toString());
%>
</head>
<body>
<table  style="border-collapse: collapse; font-size: 14px" cellspacing="0" cellpadding="0" border="1" width="100%" bordercolordark="#CCCCCC" bordercolorlight="#CCCCCC" style="line-height: 120%;border-color:#000000;font-size:10px">
	<tr  bgcolor="#e8e8e0" align="center">
		<td>姓名</td>
		<td>编号</td>
		<% for (int i=0; i<vacationType.getRowCount(); i++) { %>
			<td><%= vacationType.getValue(i, "holsName") %></td>
		<% } %>
	</tr>
	<% for (int i=0; i<vacationCount.getRowCount(); i++) { %>
		<tr align="center">
			<td><%= vacationCount.getValue(i, "bname") %></td>
			<td><%= vacationCount.getValue(i, "bcode") %></td>
			<% for (int j=0; j<vacationType.getRowCount(); j++) { %>
				<td><%= vacationCount.getValue(i, vacationType.getValue(j, "holsCode")) %></td>
			<% } %>
		</tr>
	<% } %>
	
</table>
</body>
</html>